<?php
	header('Content-Type: text/html;charset=UTF-8');
?>
<?php
include('../../Redirect_modules.php');

while(!UserSyear())
{
    session_write_close();
    session_start();
}

$current_hour = date('H');

switch (User('PROFILE'))
{
    case 'admin':
        DrawBC ($welcome.' | Role: Administrator');
           
		//absence for subject 2 day a course
		
		$sql = "SELECT s.STUDENT_ID, CONCAT(s.LAST_NAME, ' ', s.FIRST_NAME) AS NAME , cp.TITLE, COUNT(*) AS ABSENCE_TOTAL
			FROM attendance_period ap JOIN students s ON ap.STUDENT_ID = s.STUDENT_ID JOIN course_periods cp ON ap.COURSE_PERIOD_ID = cp.COURSE_PERIOD_ID
			WHERE attendance_code
			IN (
				SELECT ID
				FROM attendance_codes
				WHERE SYEAR =  '".UserSyear()."'
				AND SCHOOL_ID =  '".UserSchool()."'
				AND STATE_CODE =  'A'
			)
			AND ap.MARKING_PERIOD_ID = '".UserMP()."'
			GROUP BY ap.COURSE_PERIOD_ID, STUDENT_ID HAVING COUNT(*) > 2";

		$result = DBGet(DBQuery($sql));
		$columns = array('NAME'=>'Student', 'STUDENT_ID'=>'Student ID', 'TITLE' =>'Course', 'ABSENCE_TOTAL' =>'Total absence');
		ListOutput($result,$columns,'Student absence more than 2 day in a course','Students absence more than 2 day in a course');
		
		//absence for 2 day in a week
		
		$sql = "SELECT s.STUDENT_ID, CONCAT(s.LAST_NAME, ' ', s.FIRST_NAME) AS NAME, WEEK(ap.SCHOOL_DATE) AS WEEK, COUNT(*) AS ABSENCE_TOTAL
			FROM attendance_period ap JOIN students s ON ap.STUDENT_ID = s.STUDENT_ID JOIN course_periods cp ON ap.COURSE_PERIOD_ID = cp.COURSE_PERIOD_ID 
			WHERE attendance_code
			IN (
				SELECT ID
				FROM attendance_codes
				WHERE SYEAR =  '".UserSyear()."'
				AND SCHOOL_ID =  '".UserSchool()."'
				AND STATE_CODE =  'A'
			)
			AND ap.MARKING_PERIOD_ID = '".UserMP()."'
			GROUP BY STUDENT_ID, WEEK(ap.SCHOOL_DATE) HAVING COUNT(*) > 2 ORDER BY WEEK(ap.SCHOOL_DATE) ASC";
		$functions = array('WEEK' =>'_makeWeekOfAbsence'); 
		$result = DBGet(DBQuery($sql), $functions);
		$columns = array('NAME'=>'Student', 'STUDENT_ID'=>'Student ID', 'WEEK' => 'Week', 'ABSENCE_TOTAL' =>'Total absence');
		ListOutput($result,$columns,'Student absence more than 2 day in a week','Students absence more than 2 day in a week');

        break;

}

function _nl2br($value,$column)
{
    return nl2br($value);
}
function _makeWeekOfAbsence($value,$title)
{	global $THIS_RET;
	$gendate_start = new DateTime();
	$gendate_start->setISODate(date('Y'), $THIS_RET['WEEK'] + 1, 1);
	
	$gendate_end = new DateTime();
	$gendate_end->setISODate(date('Y'), $THIS_RET['WEEK'] + 1, 7);
	return $gendate_start->format('Y/m/d') .' - '. $gendate_end->format('Y/m/d');
}

?>